﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    class ProductDB
    {
        static string connectionString = "data source=.;Initial Catalog=ShopDB;Integrated Security=True";

        public void Add(Product p)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = string.Format("INSERT INTO MyTable (barCode, name) VALUES('{0}', '{1}'", p.barCode, p.name);
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void Update(Product p)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = string.Format("UPDATE MyTable SET name = '{1}' WHERE barCode ='{0}', p.barCode, p.name");
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void Delete(Product p)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = string.Format("DELETE FROM MyTable WHERE barCode ='{0}', p.barCode");
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public List<Product> GetProducts()
        {
            List<Product> result = new List<Product>();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string quesry = "SELECT * from MyTable";
                SqlCommand command = new SqlCommand(quesry, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Product p = new Product();
                    p.barCode = (int)reader["barCode"];
                    p.name = (string)reader["name"];
                   
                    result.Add(p);
                }
                reader.Close();
            }

            return result;
        }
    }
}